/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package exercises.day03.pt02;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Student
 */
public class BookDao {
    //STEP 2: Create connection

    private final String url = "jdbc:derby://localhost:1527/webshoppe;user=root;password=root";
    private Connection conn;

    public BookDao() throws ClassNotFoundException, SQLException {
        //set driver
        Class.forName("org.apache.derby.jdbc.ClientDriver");
        //open connection
        conn = DriverManager.getConnection(url);
    }

    public boolean addBook(Book book) throws SQLException {

        PreparedStatement ps = conn.prepareStatement("INSERT INTO ROOT.BOOKSDETAILS (BID, TITLE, BOOKDESC, AUNAME, BOOKPRICE) VALUES (?,?,?,?,?)");
        ps.setString(1, book.getId());
        ps.setString(2, book.getTitle());
        ps.setString(3, book.getDescription());
        ps.setString(4, book.getAuthorName());
        ps.setInt(5, book.getPrice());

        return ps.executeUpdate() != 0 ? true : false;



    }

    public boolean deleteBook(Book book) throws SQLException {


        PreparedStatement ps = conn.prepareStatement("DELETE FROM ROOT.BOOKSDETAILS WHERE BID = ?");
        ps.setString(1, book.getId());


        return ps.executeUpdate() != 0 ? true : false;

    }

    public ArrayList<Book> getBooksList() throws SQLException, ClassNotFoundException {
        ArrayList<Book> books = new ArrayList<Book>();

        PreparedStatement ps = conn.prepareStatement("select * from ROOT.BOOKSDETAILS  ");

        ResultSet rs = ps.executeQuery();

        //STEP 5: Iterate through the results set
        Book book = null;

        while (rs.next()) {
            book = new Book(rs.getString("BID"), 
                    rs.getString("TITLE"), 
                    rs.getString("BOOKDESC"),                     
                    rs.getString("AUNAME"), 
                    rs.getInt("BOOKPRICE"));            
            books.add(book);
        }
        return books;

    }

    public ArrayList<Book> getAuthorBook(Book book) throws SQLException, ClassNotFoundException {

        ArrayList<Book> books = new ArrayList<Book>();

        PreparedStatement ps = conn.prepareStatement("select * from ROOT.BOOKSDETAILS where auname = ? ");
        ps.setString(1, book.getAuthorName());


        ResultSet rs = ps.executeQuery();

        //STEP 5: Iterate through the results set

         book = null;

        while (rs.next()) {
            book = new Book(rs.getString("BID"), rs.getString("TITLE"), rs.getString("AUNAME"), rs.getInt("BOOKPRICE"));
            book.setDescription(rs.getString("BOOKDESC"));
            books.add(book);

        }
        return books;


    }
    
    public boolean update(Book book) throws SQLException{
        PreparedStatement ps = conn.prepareStatement("update ROOT.BOOKSDETAILS set auname = ?, set bookprice= ? where  bid = ?");
        ps.setString(1, book.getAuthorName());
        ps.setInt(2, book.getPrice());
        ps.setString(3, book.getId());
        
        return ps.executeUpdate() != 0 ? true : false;
    }
    

    public boolean updateBookAuthor(Book book, String newAuthor) throws SQLException {

        PreparedStatement ps = conn.prepareStatement("update ROOT.BOOKSDETAILS set auname = ? where  bid = ?");
        ps.setString(1, newAuthor);
        ps.setString(2, book.getId());

        return ps.executeUpdate() != 0 ? true : false;

    }

    public boolean updateBookPrice(Book book, int price) throws SQLException {

        PreparedStatement ps = conn.prepareStatement("update ROOT.BOOKSDETAILS set bookprice = ? where  bid = ?");
        ps.setInt(1, price);
        ps.setString(2, book.getId());

        return ps.executeUpdate() != 0 ? true : false;

    }
}
